﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public class TelRecordDAL
    {
        public static readonly SqlConnectionStringBuilder DispatchBuilder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);//取调度库的链接字符串
        public static readonly string DispatchDatabase = DispatchBuilder.InitialCatalog;

        //查询电话流水
        public object GetTelRecordList(int pageSize, int pageIndex, DateTime start, DateTime end, string callPhone, string workCode, string deskCode, string resultType, string isCallOut,
                                       string isInLine, string isWireless, E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID, string eventDispatchCenterID)
        {
//            List<TelRecordInfo> list = new List<TelRecordInfo>();
//            StringBuilder sb = new StringBuilder();
//            sb.Append(@"select
//                         identity(int,1,1) as 行号,
//                         编码=ttl.编码,
//                         电话号码=tac.主叫号码,
//                         是否呼出=case when tac.是否呼出=0 then '呼入' else '呼出' end,
//                         来电时刻=ttl.产生时刻,
//                         震铃时刻=ttl.震铃时刻
//                        ,调度员工号=ttl.调度员工号,
//                        姓名= tp.姓名,
//                        台号=td.显示名称,
//                        结果类型=tztlr.名称,
//                        录音号=ttl.录音号
//                        into #tempa 
//                        from TTelLog ttl 
//                        left join TPerson tp on tp.工号=ttl.调度员工号 
//                        left join TDesk td on td.台号=ttl.台号
//                        left join TAlarmCall tac on tac.录音号=ttl.录音号
//                        left join TZTelLogResult tztlr on tztlr.编码=ttl.结果编码
//                        where ttl.产生时刻>'" + start + "' and ttl.产生时刻<='" + end + "' ");
//            WhereClauseUtility.AddStringLike("ttl.主叫号码", callPhone, sb);
//            WhereClauseUtility.AddStringLike("ttl.调度员工号", workCode, sb);
//            WhereClauseUtility.AddStringLike("ttl.台号", deskCode, sb);
//            WhereClauseUtility.AddStringEqual("ttl.结果类型", resultType, sb);
//            WhereClauseUtility.AddStringEqual("ttl.是否呼出", isCallOut, sb);
//            //WhereClauseUtility.AddStringEqual("ttr.是否排队", isInLine, sb);
//            //WhereClauseUtility.AddStringEqual("ttr.是否无线", isWireless, sb);

//            if (eventDispatchCenterID == "1")
//                sb.Append(" and ttl.中心编码<=6 ");
//            else
//                WhereClauseUtility.AddStringEqual("ttl.中心编码", eventDispatchCenterID, sb); //2021-06-10

//            sb.Append(@" 
//                    order by ttl.产生时刻 desc
//                    select * into #temp from #tempa where 1=1 ");

//            sb.Append(@"
//                    select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
//            sb.Append(@"
//                    SELECT count(*) FROM #temp t
//                    drop table #tempa,#temp ");

            //add2021-07-02 plq 适配上海V8 电话流水从TTelLog表(无是否呼出及结果类型字段)取，移植上海V8FA相关SQL
            List<TelRecordInfo> list = new List<TelRecordInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select identity(int,1,1) as 行号,编码=ttr.编码,
                            电话号码=ttr.对方电话,
                            是否呼出=case when ttr.呼入时刻 is not null and  ttr.记录类型编码 not in (6,10) then '呼入'
                            when ttr.记录类型编码 in (6,10) then '呼出' end,
                            来电时刻=ttr.呼入时刻,
                            震铃时刻=ttr.震铃时刻,
                            调度员工号=tp.工号,
                            姓名= tp.姓名,
                            台号=td.显示名称,
                            结果类型=tzlr.名称,
                            录音号=ttr.录音号,
                            ttr.产生时刻
                            into #tempa 
                            from TTelLog ttr 
                            left join TPerson tp on tp.编码=ttr.调度员工号 
                            left join TDesk td on td.台号=ttr.台号
                            left join TZTelLogResult tzlr on ttr.结果编码 = tzlr.编码
                            where ttr.产生时刻>'" + start + "' and ttr.产生时刻<='" + end + "'  ");
            WhereClauseUtility.AddStringLike("ttr.对方电话", callPhone, sb);
            WhereClauseUtility.AddStringLike("tp.工号", workCode, sb);
            WhereClauseUtility.AddStringLike("ttr.台号", deskCode, sb);
            WhereClauseUtility.AddStringEqual("ttr.结果编码", resultType, sb);
            if (isCallOut == "0")
                sb.Append(@" and ttr.呼入时刻 is not null and ttr.记录类型编码 not in (6,10) ");//6是呼出，10是非急救呼出
            else if (isCallOut == "1")
            {
                sb.Append(@"   and ttr.记录类型编码 in (6,10)  ");
            }
            if (isInLine == "0")
            {
                sb.Append(@" and ttr.排队时刻 is null");
            }
            else if (isInLine == "1")
                sb.Append(@" and ttr.排队时刻 is not null");
            //WhereClauseUtility.AddStringEqual("ttr.是否呼出", isCallOut, sb);
            //WhereClauseUtility.AddStringEqual("ttr.是否排队", isInLine, sb);
            //   WhereClauseUtility.AddStringEqual("ttr.是否无线", isWireless, sb);

            WhereClauseUtility.AddStringEqual("ttr.中心编码", eventDispatchCenterID, sb); //2020-11-22 zch

            sb.Append(@" 
                    order by ttr.呼入时刻 desc
                    select * into #temp from #tempa where 1=1 ");
            sb.Append(@"
                    select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
            sb.Append(@"
                    SELECT count(*) FROM #temp t
                    drop table #tempa,#temp ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    TelRecordInfo info = new TelRecordInfo();
                    info.callPhone = dr["电话号码"].ToString();
                    info.isCallOut = dr["是否呼出"].ToString();
                    info.callTime = dr["来电时刻"].ToString();
                    info.CallTime = Convert.ToDateTime(dr["产生时刻"]); //add2021-07-03 plq---用于获取录音文件的年月日规则存储目录
                    info.shockTime = dr["震铃时刻"].ToString();
                    info.workCode = dr["调度员工号"].ToString();
                    info.name = dr["姓名"].ToString();
                    info.deskCode = dr["台号"].ToString();
                    info.resultType = dr["结果类型"].ToString();
                    info.recordNumber = dr["录音号"].ToString();
                    //info.telRecordcode = Convert.ToInt32(dr["编码"]);
                    info.telRecordcode = dr["编码"].ToString();// Convert.ToInt32(dr["编码"]);
                    list.Add(info);
                }
            }

            int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            var result = new { total = total, rows = list };
            return result;
        }

        //获取台号
        public object GetDeskCodeList(string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 台号,显示名称 from TDesk where 是否有效 = 1");
            if (eventDispatchCenterID == "1")
                sb.Append(" and 中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("中心编码", eventDispatchCenterID, sb); //2021-06-05 gdg
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["台号"].ToString();
                    info.Name = dr["显示名称"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取TelLog结果类型字典
        /// add2021-07-02 plq
        /// </summary>
        /// <returns></returns>
        public object GetCallResultType()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        select 编码,名称 from TZTelLogResult
                        
                        where 是否有效=1 
                        ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            List<P_User> list = new List<P_User>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                P_User info = new P_User();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new P_User();
                    info.ID = Convert.ToInt32(dr["编码"]);
                    info.Name = dr["名称"].ToString();

                    list.Add(info);
                }
            }
            return list;
        }
    }
}

